package com.etooling.pdm.repository;

import com.etooling.framework.repository.BaseRepository;
import com.etooling.pdm.entity.GoodsType;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Map;

/**
 * 查询产品分类接口
 *
 * @author dyz
 * @date 2023/2/24 16:11
 * @description GoodsTypeRepository
 */
@Repository
public interface GoodsTypeRepository extends BaseRepository<GoodsType> {

    /**
     * 查询所有指定条件的分类信息
     *
     * @param companyId
     * @param status
     * @param isProduct
     * @return
     */
    @Query(nativeQuery = true, value = "" +
            "select gt.id, " +
            "       gt.company_id, " +
            "       gt.create_time, " +
            "       gt.is_product, " +
            "       gt.last_modified_time, " +
            "       gt.parent_id, " +
            "       gt.seq, " +
            "       gt.status, " +
            "       gt.type_code, " +
            "       gt.type_name, " +
            "       gt.creator_id, " +
            "       e1.name creator_name, " +
            "       gt.modifier_id, " +
            "       e2.name modifier_name, " +
            "       gt.is_components, " +
            "       gt.type_code_path, " +
            "       gt.type_name_path " +
            "from pdm.t_goods_type gt " +
            "         left join framework.t_employee e1 on e1.id = gt.creator_id " +
            "         left join framework.t_employee e2 on e2.id = gt.modifier_id " +
            "where gt.company_id = ?1 " +
            "  and gt.status = ?2 " +
            "  and gt.is_product = ?3 ")
    List<Map<String, Object>> getAllIsProductGoodsTypeData(String companyId, String status, Boolean isProduct);


    /**
     * 查询所有指定条件的分类信息
     *
     * @param companyId
     * @param status
     * @return
     */
    @Query(nativeQuery = true, value = "" +
            "select gt.id, " +
            "       gt.company_id, " +
            "       gt.create_time, " +
            "       gt.is_product, " +
            "       gt.last_modified_time, " +
            "       gt.parent_id, " +
            "       gt.seq, " +
            "       gt.status, " +
            "       gt.type_code, " +
            "       gt.type_name, " +
            "       gt.creator_id, " +
            "       e1.name creator_name, " +
            "       gt.modifier_id, " +
            "       e2.name modifier_name, " +
            "       gt.is_components, " +
            "       gt.type_code_path, " +
            "       gt.type_name_path " +
            "from pdm.t_goods_type gt " +
            "         left join framework.t_employee e1 on e1.id = gt.creator_id " +
            "         left join framework.t_employee e2 on e2.id = gt.modifier_id " +
            "where gt.company_id = ?1 " +
            "  and gt.status = ?2 ")
    List<Map<String, Object>> getAllGoodsTypeData(String companyId, String status);


    @Query(nativeQuery = true, value = "select count(*) from pdm.t_goods_type where type_code=?1 and parent_id=?2 and status !=?3 and is_product = ?4 ")
    Integer countTypeCode(String typeCode, String parentId, String neStauts, Boolean isProduct);


    @Query(nativeQuery = true, value = "select coalesce(max(seq),0) from pdm.t_goods_type where parent_id=?1 and company_id=?2")
    Integer getMaxSeq(String parentId, String companyId);


    @Query(nativeQuery = true, value = "select count(seq) cs from pdm.t_goods_type where parent_id=?1 and company_id=?2 group by seq HAVING count(seq) > 1")
    List<Integer> checkSeq(String parentId, String companyId);


    @Transactional
    @Modifying()
    @Query(nativeQuery = true, value = "update pdm.t_goods_type set seq = (case when seq = ?1 then ?2 when seq= ?2 then ?1 end )where seq in (?1,?2) and parent_id=?3 and company_id=?4 ")
    void updateSeq(Integer oldSeq, Integer newSeq, String parentId, String companyId);


}
